import openpyxl
import win32com.client

from tools_pmc import (today_str1,
                       fill_blue, fill_yellow, fill_orange,
                       font_white,
                       lj_tyl, lj_zyl, )


def zyl():
    wb_tyl = openpyxl.load_workbook(lj_tyl)
    ws_tyl = wb_tyl.worksheets[0]

    wb = openpyxl.Workbook()
    ws_1 = wb.worksheets[0]
    ws_1.title = "品号主键"
    wb.create_sheet("规格主键", 1)
    ws_2 = wb.worksheets[1]
    wb.create_sheet("系列主键", 2)
    ws_3 = wb.worksheets[2]

    # 品号主键
    a = 2
    temp = ws_tyl.cell(2, 1).value
    for i in range(2, ws_tyl.max_row + 1):
        if ws_tyl.cell(i, 1).value != ws_tyl.cell(i - 1, 1).value \
                and ws_tyl.cell(i, 1).value != ws_tyl.cell(i + 1, 1).value:
            for j in range(1, 4):
                ws_1.cell(a, j).value = ws_tyl.cell(i, j + 3).value
                ws_1.cell(a, j + 3).value = ws_tyl.cell(i, j).value
            ws_1.cell(a, 7).value = ws_tyl.cell(i, 7).value
            ws_1.cell(a, 8).value = ws_tyl.cell(i, 11).value
            ws_1.cell(a, 9).value = ws_tyl.cell(i, 12).value
            ws_1.cell(a, 10).value = ws_tyl.cell(i, 18).value
            ws_1.cell(a, 11).value = ws_tyl.cell(i, 19).value
            ws_1.cell(a, 12).value = ws_tyl.cell(i, 21).value
            ws_1.cell(a, 1).number_format = '0'
            ws_1.cell(a, 4).number_format = '0'
            ws_1.cell(a, 8).number_format = '0'
            a += 1

    list1 = ["成品品号", "成品品名", "成品规格", "专用料品名", "专用料品名", "专用料规格", "组成用量", "备注一",
             "备注一库存", "可用数量",
             "在途在检", "已请未购"]
    list2 = [14, 28, 21, 14, 28, 21, 10, 14, 10, 10, 10, 10]
    for i in range(1, 13):
        ws_1.column_dimensions[openpyxl.utils.get_column_letter(i)].width = list2[i - 1]
        ws_1.cell(1, i).value = list1[i - 1]

    ws_1.cell(1, 1).fill = fill_blue
    ws_1.cell(1, 2).fill = fill_blue
    ws_1.cell(1, 3).fill = fill_blue
    ws_1.cell(1, 4).fill = fill_orange
    ws_1.cell(1, 5).fill = fill_orange
    ws_1.cell(1, 6).fill = fill_orange
    ws_1.cell(1, 10).fill = fill_yellow
    range_active = "A1:L" + str(ws_1.max_row)
    ws_1.auto_filter.ref = range_active
    ws_1.freeze_panes = 'A2'  # 冻结窗口

    # 规格主键
    a = 2
    set1 = {1, 2}
    for i in range(2, ws_tyl.max_row + 1):
        if ws_tyl.cell(i, 1).value == ws_tyl.cell(i - 1, 1).value:
            set1.add(ws_tyl.cell(i, 6).value)
        else:
            if len(set1) == 1:
                ws_2.cell(a, 1).value = ws_tyl.cell(i - 1, 6).value
                ws_2.cell(a, 2).value = ws_tyl.cell(i - 1, 5).value
                ws_2.cell(a, 3).value = ws_tyl.cell(i - 1, 1).value
                ws_2.cell(a, 4).value = ws_tyl.cell(i - 1, 2).value
                ws_2.cell(a, 5).value = ws_tyl.cell(i - 1, 3).value
                ws_2.cell(a, 6).value = ws_tyl.cell(i - 1, 7).value
                ws_2.cell(a, 7).value = ws_tyl.cell(i - 1, 11).value
                ws_2.cell(a, 8).value = ws_tyl.cell(i - 1, 12).value
                ws_2.cell(a, 9).value = ws_tyl.cell(i - 1, 18).value
                ws_2.cell(a, 10).value = ws_tyl.cell(i - 1, 19).value
                ws_2.cell(a, 11).value = ws_tyl.cell(i - 1, 21).value
                ws_2.cell(a, 3).number_format = '0'
                ws_1.cell(a, 7).number_format = '0'
                a += 1
            set1 = set()
            set1.add(ws_tyl.cell(i, 6).value)

    list1 = ["成品规格", "成品品名", "专用料品名", "专用料品名", "专用料规格", "组成用量", "备注一",
             "备注一库存", "可用数量",
             "在途在检", "已请未购"]
    list2 = [21, 28, 14, 28, 21, 10, 14, 10, 10, 10, 10]
    for i in range(1, 12):
        ws_2.column_dimensions[openpyxl.utils.get_column_letter(i)].width = list2[i - 1]
        ws_2.cell(1, i).value = list1[i - 1]

    ws_2.cell(1, 1).fill = fill_blue
    ws_2.cell(1, 2).fill = fill_blue
    ws_2.cell(1, 3).fill = fill_orange
    ws_2.cell(1, 4).fill = fill_orange
    ws_2.cell(1, 5).fill = fill_orange
    ws_2.cell(1, 9).fill = fill_yellow
    range_active = "A1:K" + str(ws_2.max_row)
    ws_2.auto_filter.ref = range_active
    ws_2.freeze_panes = 'A2'  # 冻结窗口
    # 系列主键

    a = 2
    set1 = {1, 2}
    for i in range(2, ws_tyl.max_row + 1):
        if ws_tyl.cell(i, 1).value == ws_tyl.cell(i - 1, 1).value:
            str1 = ws_tyl.cell(i, 6).value
            set1.add(str1[0:str1.find("-")])
        else:
            if len(set1) == 1:
                ws_3.cell(a, 1).value = ws_tyl.cell(i - 1, 6).value[0:ws_tyl.cell(i - 1, 6).value.find("-")]
                ws_3.cell(a, 2).value = ws_tyl.cell(i - 1, 1).value
                ws_3.cell(a, 3).value = ws_tyl.cell(i - 1, 2).value
                ws_3.cell(a, 4).value = ws_tyl.cell(i - 1, 3).value
                ws_3.cell(a, 5).value = ws_tyl.cell(i - 1, 7).value
                ws_3.cell(a, 6).value = ws_tyl.cell(i - 1, 11).value
                ws_3.cell(a, 7).value = ws_tyl.cell(i - 1, 12).value
                ws_3.cell(a, 8).value = ws_tyl.cell(i - 1, 18).value
                ws_3.cell(a, 9).value = ws_tyl.cell(i - 1, 19).value
                ws_3.cell(a, 10).value = ws_tyl.cell(i - 1, 21).value
                ws_3.cell(a, 2).number_format = '0'
                ws_1.cell(a, 6).number_format = '0'
                a += 1
            set1 = set()
            str1 = ws_tyl.cell(i, 6).value
            if str1 is not None:
                set1.add(str1[0:str1.find("-")])

    list1 = ["系列规格", "专用料品名", "专用料品名", "专用料规格", "组成用量", "备注一",
             "备注一库存", "可用数量",
             "在途在检", "已请未购"]
    list2 = [21, 14, 28, 21, 10, 14, 10, 10, 10, 10]
    for i in range(1, 11):
        ws_3.column_dimensions[openpyxl.utils.get_column_letter(i)].width = list2[i - 1]
        ws_3.cell(1, i).value = list1[i - 1]

    ws_3.cell(1, 1).fill = fill_blue
    ws_3.cell(1, 2).fill = fill_orange
    ws_3.cell(1, 2).fill = fill_orange
    ws_3.cell(1, 3).fill = fill_orange
    ws_3.cell(1, 8).fill = fill_yellow
    range_active = "A1:J" + str(ws_3.max_row)
    ws_3.auto_filter.ref = range_active
    ws_3.freeze_panes = 'A2'  # 冻结窗口

    wb.save(lj_zyl)
    wb.close()

    excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    excel.DisplayAlerts = False

    e_wb_zyl = excel.Workbooks.Open(lj_zyl)
    for i in range(1, 4):
        e_ws_zyl = e_wb_zyl.Worksheets(i)
        e_ws_zyl.Sort.SortFields.Add(Key=e_ws_zyl.Range("A2"),
                                     Order=win32com.client.constants.xlAscending)  # 添加排序条件，按顺序添加
        e_ws_zyl.Sort.SetRange(
            e_ws_zyl.Range("A2:L" + str(e_ws_zyl.UsedRange.Rows.Count)))  # 这里需要注意，设置需要排序的范围。
        e_ws_zyl.Sort.Apply()  # 执行排序
    e_wb_zyl.SaveAs(lj_zyl)
    e_wb_zyl.Close()

    wb = openpyxl.load_workbook(lj_zyl)
    for i in range(0, 3):
        ws = wb.worksheets[i]
        for j in range(ws.max_row, 1, -1):
            if ws.cell(j, 1).value == ws.cell(j - 1, 1).value:
                if i == 0:
                    ws.cell(j, 1).font = font_white
                    ws.cell(j, 2).font = font_white
                    ws.cell(j, 3).font = font_white
                elif i == 1:
                    ws.cell(j, 1).font = font_white
                    ws.cell(j, 2).font = font_white
                else:
                    ws.cell(j, 1).font = font_white
    wb.save(lj_zyl)
    print(today_str1 + ": zyl succeed!")
